﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
/// <summary>
/// Summary description for TimKiemCtr
/// </summary>
public class TimKiemCtr
{
	public TimKiemCtr()
	{
		
	}
    public DataTable TimKiem(string tenSP,string loaisp)
    {
        try
        {
            string sql = "select * from sanpham where (tensp like N'" + Exec(tenSP) + "'" + " OR TenSP LIKE N'" + Exec(ConvertVN.Convert(tenSP)) + "' OR dbo.fChuyenCoDauThanhKhongDau(TenSP) like N'" + Exec(tenSP) + "'" + " OR dbo.fChuyenCoDauThanhKhongDau(TenSP) LIKE N'" + Exec(ConvertVN.Convert(tenSP)) + "') and loaisp in (select loaisp from phanloai where loaisp=@loaiSP)";
            return DataProvider.GetData(sql,"@loaiSP", loaisp);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    // Phương thức Convert một chuỗi ký tự Có dấu sang Không dấu
    public static string Convert(string chucodau)
    {
        const string FindText = "áàảãạâấầẩẫậăắằẳẵặđéèẻẽẹêếềểễệíìỉĩịóòỏõọôốồổỗộơớờởỡợúùủũụưứừửữựýỳỷỹỵÁÀẢÃẠÂẤẦẨẪẬĂẮẰẲẴẶĐÉÈẺẼẸÊẾỀỂỄỆÍÌỈĨỊÓÒỎÕỌÔỐỒỔỖỘƠỚỜỞỠỢÚÙỦŨỤƯỨỪỬỮỰÝỲỶỸỴ";
        const string ReplText = "aaaaaaaaaaaaaaaaadeeeeeeeeeeeiiiiiooooooooooooooooouuuuuuuuuuuyyyyyAAAAAAAAAAAAAAAAADEEEEEEEEEEEIIIIIOOOOOOOOOOOOOOOOOUUUUUUUUUUUYYYYY";
        int index = -1;
        char[] arrChar = FindText.ToCharArray();
        while ((index = chucodau.IndexOfAny(arrChar)) != -1)
        {
            int index2 = FindText.IndexOf(chucodau[index]);
            chucodau = chucodau.Replace(chucodau[index], ReplText[index2]);
        }
        return chucodau;
    }
    // Phương thức trả về câu lệnh SQL dùng truy vấn dữ liệu
    public static string ApproximateSearch(string keyWord)
    {
        string sql = "SELECT * FROM Employer WHERE FullName LIKE ";
        return sql + "N'" + Exec(keyWord) + "' OR FullName LIKE N'" + Exec(ConvertVN.Convert(keyWord)) + "'";
    }

    // Phương thức chuyển đổi một chuỗi ký tự: Nếu chuỗi đó có ký tự " " sẽ thay thế bằng "%"
    public static string Exec(string keyWord)
    {
        string[] arrWord = keyWord.Split(' ');
        StringBuilder str = new StringBuilder("%");
        for (int i = 0; i < arrWord.Length; i++)
        {
            str.Append(arrWord[i] + "%");
        }
        return str.ToString();
    }
    public DataTable TimKiemSPTheoTen(string tensp)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and SanPham.TrangThai='True' and (tensp like N'" + Exec(tensp) + "'" + " OR TenSP LIKE N'" + Exec(ConvertVN.Convert(tensp)) + "' OR dbo.fChuyenCoDauThanhKhongDau(TenSP) like N'" + Exec(tensp) + "'" + " OR dbo.fChuyenCoDauThanhKhongDau(TenSP) LIKE N'" + Exec(ConvertVN.Convert(tensp)) + "')";
            //return DataProvider.GetData(sql);
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemSPTheoGioiTinh(string gioiTinh)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and SanPham.TrangThai='True' and DanhChoGioiTinh=@tenSP";
            //return DataProvider.GetData(sql);
            return DataProvider.GetData(sql, "@tenSP",gioiTinh);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemLoaiSPTheoTen(string tenLoai)
    {
        try
        {
            string sql = "select * from PhanLoai where TrangThai='True' and tenLoaisp like @TenLoai";
            //return DataProvider.GetData(sql);
            return DataProvider.GetData(sql, "@TenLoai","%"+ tenLoai+"%");
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemDanhMucTheoTen(string tenDanhMuc)
    {
        try
        {
            string sql = "select * from DanhMuc where TrangThai='True' and tendanhmuc like @TenLoai";
            //return DataProvider.GetData(sql);
            return DataProvider.GetData(sql, "@TenLoai", "%" + tenDanhMuc + "%");
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemThanhVienTheoTen(string tenKH)
    {
        try
        {
            string sql = "SELECT a.TenDangNhap,a.TenKH,a.Email,Count(b.mahd) as sohd,Sum((tongtien-giamgia)) as tongtien  FROM ThanhVien a,DonDatHang b where a.TrangThai=1 and a.TenDangNhap=b.KhachHang and tenKH like @tenKH group by a.TenDangNhap,a.TenKH,a.Email Union" +
" SELECT distinct a.TenDangNhap,a.TenKH,a.Email,0 as sohd,0 as tongtien  FROM ThanhVien a,DonDatHang b where a.TrangThai=1 and tenKH like @tenKH and a.TenDangNhap not in (select KhachHang from DonDatHang )  ";
            return DataProvider.GetData(sql, "@tenKH","%"+ tenKH+"%");
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemThanhVienTheoCMND(string cmnd)
    {
        try
        {
            string sql = "SELECT a.TenDangNhap,a.TenKH,a.Email,Count(b.mahd) as sohd,Sum((tongtien-giamgia)) as tongtien  FROM ThanhVien a,DonDatHang b where a.TrangThai=1 and a.TenDangNhap=b.KhachHang and cmnd =@CMND group by a.TenDangNhap,a.TenKH,a.Email Union" +
" SELECT distinct a.TenDangNhap,a.TenKH,a.Email,0 as sohd,0 as tongtien  FROM ThanhVien a,DonDatHang b where a.TrangThai=1 and cmnd =@CMND and a.TenDangNhap not in (select KhachHang from DonDatHang )  ";
            return DataProvider.GetData(sql, "@CMND",cmnd);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemThanhVienTheoSoDienThoai(string sodienthoai)
    {
        try
        {
            string sql = "SELECT a.TenDangNhap,a.TenKH,a.Email,Count(b.mahd) as sohd,Sum((tongtien-giamgia)) as tongtien  FROM ThanhVien a,DonDatHang b where a.TrangThai=1 and a.TenDangNhap=b.KhachHang and dienthoai=@sodienthoai group by a.TenDangNhap,a.TenKH,a.Email Union" +
" SELECT distinct a.TenDangNhap,a.TenKH,a.Email,0 as sohd,0 as tongtien  FROM ThanhVien a,DonDatHang b where a.TrangThai=1 and dienthoai=@sodienthoai and a.TenDangNhap not in (select KhachHang from DonDatHang )  ";
            return DataProvider.GetData(sql, "@sodienthoai", sodienthoai);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemTinTucTheoTen(string tenTinTuc)
    {
        try
        {
            string sql = "select *,TenLoai from TinTuc,LoaiTinTuc where TinTuc.MaTT=LoaiTinTuc.MaLoaiTT and TrangThai=1 and TieuDe like N'%" + tenTinTuc + "%'";
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    public DataTable TimKiemTheoLoaiSP(int loaisp)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and TinhTrang='True' and SanPham.loaisp in (select loaisp from phanloai where loaisp=" + loaisp + ")";
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    public DataTable TimKiemTheoLoaiSP_GiaTu(int loaisp,int giaTu)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and TrangThai='True' and SanPham.loaisp in (select loaisp from phanloai where loaisp= " + loaisp + " and gia>="+giaTu+" )";
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemTheoLoaiSP_GiaDen(int loaisp, int giaDen)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and TrangThai='True' and SanPham.loaisp in (select loaisp from phanloai where loaisp= " + loaisp + " and gia<=" + giaDen + " )";
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
    public DataTable TimKiemTheoLoaiSP_GiaTu_GiaDen(int loaisp, int giaTu,int giaDen)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and TrangThai='True' and SanPham.loaisp in (select loaisp from phanloai where loaisp= " + loaisp + " and gia between " + giaTu + " and "+giaDen+" )";
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    public DataTable timSPTheoGiaTu(int giaTu)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and TrangThai='True' and gia >=" + giaTu;
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable timSPTheoGiaDen(int giaDen)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and TrangThai='True' and gia <=" + giaDen;
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable timSPTheoGiaTuVaGiaDen(int giaTu,int giaDen)
    {
        try
        {
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and TrangThai='True' and gia between " + giaTu+" and "+giaDen;
            return DataProvider.GetData(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable timSPTheoDoTuoi(int doTuoi)
    {
        try
        {
            int DoTuoi = doTuoi + 1;
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and SanPham.TrangThai='True' and dotuoi >=@DoTuoi and doTuoi <=@DoTuoi1";
            return DataProvider.GetData(sql, "@DoTuoi",doTuoi,"@DoTuoi1",DoTuoi);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable timSPTuDoTuoiDenDoTuoi(int doTuoiTu,int doTuoiDen)
    {
        try
        {
            //int DoTuoi = doTuoi + 1;
            string sql = "select *,TenLoaiSP from sanpham,PhanLoai where SanPham.LoaiSP=PhanLoai.LoaiSP and SanPham.TrangThai='True' and danhchodotuoi >=@DoTuoiTu and danhchodoTuoi <=@DoTuoiDen";
            return DataProvider.GetData(sql, "@DoTuoiTu", doTuoiTu,"@DoTuoiDen", doTuoiDen);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable timSPBanChay(int soThang)
    {
        try
        {

            string sql = "select top 10 sp.*,TenLoaiSP,sum(ctddh.Soluong) as SL from SanPham sp,PhanLoai pl,ChiTietDonDatHang ctddh,DonDatHang ddh where sp.LoaiSP=pl.LoaiSp and ddh.HinhThucTT>=2 and sp.masp=ctddh.masp and ctddh.mahd=ddh.mahd and DATEDIFF ( month,ddh.NgayLap,getDate())<=@SoThang group by sp.masp,sp.Tensp,sp.LoaiSP,sp.Gia,sp.SoLuong,sp.NgayDang,sp.HinhAnh,sp.NoiSX,sp.ChitietSP,sp.DoTuoi,sp.TrangThai,TenLoaiSP Order by SL DESC";
            return DataProvider.GetData(sql,"@SoThang",soThang);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable timSPBanChayTuNgayDenNgay(DateTime tuNgay,DateTime denNgay)
    {
        try
        {

            string sql = "select top 10 sp.*,TenLoaiSP,sum(ctddh.Soluong) as SL from SanPham sp,PhanLoai pl,ChiTietDonDatHang ctddh,DonDatHang ddh where sp.LoaiSP=pl.LoaiSp and ddh.TrangThai>=2 and ddh.TrangThai<5 and sp.masp=ctddh.masp and ctddh.mahd=ddh.mahd and ddh.NgayLap>=@TuNgay and ddh.NgayLap<=@DenNgay group by sp.masp,sp.Tensp,sp.LoaiSP,sp.Gia,sp.SoLuong,sp.NgayDang,sp.HinhAnh,sp.NoiSX,sp.ChitietSP,sp.DanhChoDoTuoi,sp.TrangThai,sp.DanhChoGioiTinh,sp.LuotXemSP,TenLoaiSP Order by SL DESC";
            return DataProvider.GetData(sql, "@TuNgay",tuNgay,"@DenNgay",denNgay);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}